
package com.feib.ebilling.job.dao;

import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;


/**
 * @title (#)BillFeedbackDao.java<br>
 * @description <br>
 * @author Yaude Huang<br>
 * @version 1.0.0 2011/06/03
 * @copyright Far Eastern International Bank Copyright (c) 2011<br>
 * @2011/06/03 create by Yaude Huang<br>
 */
@Repository("billFeedbackDao")
public class BillFeedbackDao extends BaseDao {
        
	public  List<Map<String, Object>> queryALLGroup() {
	    StringBuffer sql = new StringBuffer();
	    sql.append("SELECT GROUP_PK, GROUP_NO, GROUP_NAME ")	         
	       .append(" from APP_GROUP where GROUP_TYPE <> 1 order by GROUP_NO");	      
	    return this.getJdbcTemplate().queryForList(sql.toString());
	}   
	
	public  List<Map<String, Object>> queryGroupPKByGroupNo(String GroupNo) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT GROUP_PK, GROUP_TYPE, GROUP_NAME")	         
           .append(" from APP_GROUP where GROUP_NO='").append(GroupNo).append("'");	      
        return this.getJdbcTemplate().queryForList(sql.toString());
    }

	/**
     * @param virtualAccount
      * @return
     */
    public List<Map<String, Object>> queryWriteOffByGroupNo(String GroupPK, String processDate) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT a.VIRTUAL_ACCOUNT, a.PAYER_ID, a.PAYER_NAME, a.ACADEMIC_YEAR ")
	       .append(", a.SEMESTER, CONVERT(CHAR(8), b.TX_DATE, 112) as TX_DATE ")
	       .append(", CONVERT(CHAR(13), b.TX_DATE, 114) as TX_TIME, b.WRITE_OFF_PAYMENT_PK, b.AMOUNT, b.CHANNEL ")      
	       .append(" from PAYMENT a,WRITE_OFF_PAYMENT b where  (a.PAYMENT_PK = b.PAYMENT_PK) AND GROUP_PK =")
	       .append(GroupPK).append(" AND CONVERT(CHAR(8), b.WRITE_OFF_DATE, 112)='").append(processDate)
	       .append("' AND a.PAYMENT_STATUS ='5' order by a.VIRTUAL_ACCOUNT");
        return this.getJdbcTemplate().queryForList(sql.toString());
    } 
    
    public List<Map<String, Object>> queryFeedbacklogByThirdPartyAndGroupType(String GroupType, String processDate) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT A.USERNO, A.FBTIME, A.ADAY, A.ATIME, A.ERR_MSG, A.CHANNEL, ")
           .append("A.TXAMT, A.USERNO , B.* FROM FEEDBACK_LOG A  JOIN ")
           .append("(SELECT G.GROUP_PK ,G.GROUP_NO, G.GROUP_NAME, R.VIRTUAL_ACCOUNT_NO ")
           .append(",LEN(R.VIRTUAL_ACCOUNT_NO) AS length FROM REAL_ACCOUNT R  JOIN APP_GROUP G ON ")
           .append("R.GROUP_PK = G.GROUP_PK WHERE G.GROUP_TYPE ='").append(GroupType).append("') B  ON ")
           .append("LEFT(RTRIM(A.USERNO),B.length) = B.VIRTUAL_ACCOUNT_NO " )
           .append("WHERE CONVERT(CHAR(8), A.FBTIME, 112)='").append(processDate)
           .append("' AND FEEDBACK_TYPE = 2 ORDER BY A.USERNO ");
        return this.getJdbcTemplate().queryForList(sql.toString());
    } 
    
    /**
     * @param virtualAccount
      * @return
     */
    public List<Map<String, Object>> queryWriteOffFailByGroupNo(String GroupPK, String processDate) {
        
    	StringBuffer sql1 = new StringBuffer();
    	StringBuffer sql2 = new StringBuffer();
    	/* 現有設定的虛擬帳號與實體帳號對映　例如： 5237或 5238*/
        sql1.append("SELECT VIRTUAL_ACCOUNT_NO FROM REAL_ACCOUNT WHERE GROUP_PK=").append(GroupPK);
        List<Map<String, Object>> accounts = this.getJdbcTemplate().queryForList(sql1.toString());        
        String accts = "";
        StringBuffer where = new StringBuffer();
        where.append("(");
        /* 組查詢條件式 */
        for(Map<String, Object> account : accounts)
        { 
        	accts = (String)account.get("VIRTUAL_ACCOUNT_NO");        	
        	where.append(" left(USERNO,")
        	     .append(accts.length())
        	     .append(") = '")
        	     .append( accts + "' or ");
        }
        /* 去除最後一個 「or」加入「)」  */
        if (!where.toString().equals("(")){  
        	/* 有設定虛擬帳號與實體帳號對映 */
        	accts = where.toString();
        	accts = accts.substring(0, accts.length() - 3);
        	accts = accts + ")";        
	        /* 讀取符合查詢條件資料，且錯誤說明不為NULL的資料 */        
	        sql2.append("SELECT * FROM FEEDBACK_LOG")
	        .append(" WHERE CONVERT(CHAR(8), FBTIME, 112)='").append(processDate)
	        .append("' AND ERR_MSG is not null")
	        .append("  AND " + accts) 
	        .append("  ORDER BY FBTIME, UID");
        }else{
        	/* 未設定虛擬帳號與實體帳號對映 */
        	sql2.append("SELECT * FROM FEEDBACK_LOG")
	        .append(" WHERE CONVERT(CHAR(8), FBTIME, 112)='").append("99999999'");	     
        }
        return this.getJdbcTemplate().queryForList(sql2.toString());
    }   

}
